import os

from flask import Blueprint, redirect, render_template, request, session, url_for
from sqlalchemy.testing.pickleable import User

from config import UPLOADED_PHOTOS_DEST
from module_admin.entity.do.base import db
from module_admin.entity.do.role import SysRole
from module_admin.entity.do.user import SysUser, SysUserRole
from module_admin.utils.http import fail_api, success_api, table_api
from module_admin.utils.validate import str_escape

teacher = Blueprint('teacher', __name__)


@teacher.route('/teacher', methods=['GET', 'POST'])
def get_teacher():
    t_id = session.get('id')
    t_name = session.get('name')
    t_role = session.get('role')
    # 教师主页逻辑
    return render_template('Teacher_Main.html',
                           title='Teacher Page',
                           header='Welcome Teacher',
                           user_name=t_name,
                           user_id=t_id,
                           role=t_role
                           )


@teacher.post('/add_student')
def add_student():
    req_json = request.get_json(force=True)
    a = req_json.get("roleIds")
    username = str_escape(req_json.get('user_name'))
    # password = str_escape(req_json.get('password'))
    email = str_escape(req_json.get('email'))

    # if not username or not password:
    #     username = "user_name"
    #     password = "123"

    # if bool(SysUser.query.filter_by(username=username).count()):
    #     return fail_api(msg="用户已经存在")
    user = SysUser(user_name=username, email=email, password='123')
    db.session.add(user)
    role = SysRole.query.filter(SysRole.role_name == 'student').first()
    user_role = SysUserRole(user_id=user.user_id, role_id=role.role_id)
    db.session.add(user_role)
    db.session.commit()
    return success_api(msg="增加成功")


import pandas as pd
from flask import request, jsonify
from werkzeug.utils import secure_filename
import os


@teacher.post('/add_students_from_excel')
def add_students_from_excel():
    file = request.files.get('file')

    # 检查文件是否上传
    if not file:
        return jsonify({"error": "No file uploaded"}), 400

    # 保存文件到本地
    filename = secure_filename(file.filename)
    file_path = os.path.join(UPLOADED_PHOTOS_DEST, filename)  # 将文件保存到临时路径
    file.save(file_path)

    try:
        # 使用 pandas 读取 Excel 文件
        df = pd.read_excel(file_path)

        # 检查是否包含所需的列
        required_columns = {"username", "password", "email"}
        if not required_columns.issubset(df.columns):
            return jsonify({"error": "Excel file must contain 'username', 'password', and 'email' columns"}), 400

        # 遍历每一行，批量插入数据
        for _, row in df.iterrows():
            username = str_escape(row['username'])
            password = str_escape(row['password'])
            email = str_escape(row['email'])

            # 检查用户是否已经存在
            if SysUser.query.filter_by(user_name=username).count() > 0:
                continue  # 用户已存在，跳过此行

            # 创建用户对象并插入到数据库
            user = SysUser(user_name=username, email=email, password=password)
            db.session.add(user)

            # 获取角色并创建用户角色关联
            role = SysRole.query.filter(SysRole.role_name == 'student').first()
            user_role = SysUserRole(user_id=user.user_id, role_id=role.role_id)
            db.session.add(user_role)

        # 提交所有插入的记录
        db.session.commit()
        return jsonify({"message": "Students added successfully"}), 201

    except Exception as e:
        db.session.rollback()
        return jsonify({"error": str(e)}), 500

    finally:
        # 删除临时文件
        os.remove(file_path)


# @teacher.get('/student_list')
# def get_all_users():
#     # 查询所有用户
#     users = SysUser.query.all()
#
#     # 构建返回数据
#     user_list = [
#         {
#             "user_id": user.user_id,
#             "user_name": user.user_name,
#             "email": user.email,
#             # "role": [role.role_name for role in user.roles]  # 假设User模型有一个roles关联属性
#         }
#         for user in users
#     ]
#
#     return table_api(data=user_list, msg="获取成功")
# @teacher.get('/student_list')
# def get_all_users():
#     # 查找角色名为 "teacher" 的角色 ID
#     teacher_role = SysRole.query.filter(SysRole.role_name == 'student').first()
#     if not teacher_role:
#         return table_api(data=[], msg="获取成功")  # 若不存在 "teacher" 角色，直接返回空列表
#
#     # 获取所有不属于 "teacher" 角色的用户
#     users = SysUser.query.filter(
#         SysUser.user_id.in_(
#             db.session.query(SysUserRole.user_id).filter(SysUserRole.role_id == teacher_role.role_id)
#         )
#     ).all()
#
#     # 构建返回数据
#     user_list = [
#         {
#             "user_id": user.user_id,
#             "user_name": user.user_name,
#             "email": user.email,
#         }
#         for user in users
#     ]
#
#     return table_api(data=user_list, msg="获取成功")
@teacher.get('/student_list')
def get_all_users():
    # 从查询参数中获取 'name' 和 'email'
    name = request.args.get('name', '')  # 默认值为 ''
    email = request.args.get('email', '')  # 默认值为 ''
    # 查找角色名为 "student" 的角色 ID
    teacher_role = SysRole.query.filter(SysRole.role_name == 'student').first()
    if not teacher_role:
        return table_api(data=[], msg="获取成功")  # 若不存在 "student" 角色，直接返回空列表

    # 获取所有属于 "student" 角色的用户，并根据名字和邮箱进行模糊查询
    users = SysUser.query.filter(
        SysUser.user_id.in_(
            db.session.query(SysUserRole.user_id).filter(SysUserRole.role_id == teacher_role.role_id)
        )
    )
    print(name,111)

    # 如果传入了名字，进行模糊查询
    if name:
        print(name,11)
        users = users.filter(SysUser.user_name.like(f"%{name}%"))

    # 如果传入了邮箱，进行模糊查询
    if email:
        users = users.filter(SysUser.email.like(f"%{email}%"))

    # 执行查询
    users = users.all()

    # 构建返回数据
    user_list = [
        {
            "user_id": user.user_id,
            "user_name": user.user_name,
            "email": user.email,
        }
        for user in users
    ]

    return table_api(data=user_list, msg="获取成功")


@teacher.post('/edit_student/<int:student_id>')
def edit_user(student_id):
    req_json = request.get_json(force=True)
    new_username = str_escape(req_json.get('name'))
    # new_password = str_escape(req_json.get('password'))
    new_email = str_escape(req_json.get('email'))

    # 查找指定用户
    user = SysUser.query.filter_by(user_id=student_id).first()

    if not user:
        return fail_api(msg="用户不存在")

    # 更新用户信息
    if new_username:
        user.user_name = new_username
    # if new_password:
    #     user.password = new_password
    if new_email:
        user.email = new_email

    db.session.commit()
    return success_api(msg="编辑成功")


@teacher.get('/delete_student/<int:student_id>')
def delete_user(student_id):
    # 查找指定用户
    user = SysUser.query.filter_by(user_id=student_id).first()

    if not user:
        return fail_api(msg="用户不存在")

    # 删除用户角色关联
    SysUserRole.query.filter_by(user_id=student_id).delete()

    # 删除用户
    db.session.delete(user)
    db.session.commit()

    return success_api(msg="删除成功")
